package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import Clases.domain.Usuario;

public class JDBCUsuarioDAO implements IUsuarioDAO {

	public List selectAllUsuariosZona(Connection conn,Integer zonaOID) {

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE z.zonaOID=U.zonaOID and U.zonaOID = '"+zonaOID+"'";

			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setEmail(result.getString("email"));
				u.setNick(result.getString("nick"));
				u.setPassword(result.getString("password"));
				u.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					u.setRecicla(true);
				} else {
					u.setRecicla(false);
				}

				u.setAceite(result.getInt("aceite"));
				u.setMedicamentos(result.getInt("medicina"));
				u.setOrganico(result.getInt("organico"));
				u.setPapel(result.getInt("papel"));
				u.setPilas(result.getInt("pilas"));
				u.setPlastico(result.getInt("plastico"));
				u.setVidrio(result.getInt("vidrio"));
				u.setBarrio(result.getString("barrio"));
				u.setZona(result.getString("nombre"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}

	public List selectAllUsuariosManifestacion(Connection conn,Integer manifestacionOID){

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Usuario U, UsuarioManifestacion UM WHERE U.usuarioOID = UM.usuarioOID AND UM.manifestacionOID= '"+manifestacionOID+"'";
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setEmail(result.getString("email"));
				u.setNick(result.getString("nick"));
				u.setPassword(result.getString("password"));
				u.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					u.setRecicla(true);
				} else {
					u.setRecicla(false);
				}

				u.setAceite(result.getInt("aceite"));
				u.setMedicamentos(result.getInt("medicina"));
				u.setOrganico(result.getInt("organico"));
				u.setPapel(result.getInt("papel"));
				u.setPilas(result.getInt("pilas"));
				u.setPlastico(result.getInt("plastico"));
				u.setVidrio(result.getInt("vidrio"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}

	public List selectAllUsuarios() {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE Z.zonaOID=U.zonaOID ";
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();

			while (result.next()) {
				Usuario temp = new Usuario();
				temp.setEmail(result.getString("email"));
				temp.setNick(result.getString("nick"));
				temp.setPassword(result.getString("password"));
				temp.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					temp.setRecicla(true);
				} else {
					temp.setRecicla(false);
				}

				temp.setAceite(result.getInt("aceite"));
				temp.setMedicamentos(result.getInt("medicina"));
				temp.setOrganico(result.getInt("organico"));
				temp.setPapel(result.getInt("papel"));
				temp.setPilas(result.getInt("pilas"));
				temp.setPlastico(result.getInt("plastico"));
				temp.setVidrio(result.getInt("vidrio"));
				temp.setBarrio(result.getString("barrio"));
				temp.setZona(result.getString("nombre"));
				searchResults.add(temp);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}

	

	public Usuario selectUsuario(String userForm, String passwdForm) {

		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		Usuario usuario = new Usuario();

		try {
			
			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE Z.zonaOID=U.zonaOID and U.nick LIKE '"+userForm+"' AND U.password LIKE  '"+passwdForm+"'";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				usuario.setEmail(result.getString("email"));
				usuario.setNick(result.getString("nick"));
				usuario.setPassword(result.getString("password"));
				usuario.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					usuario.setRecicla(true);
				} else {
					usuario.setRecicla(false);
				}

				usuario.setAceite(result.getInt("aceite"));
				usuario.setMedicamentos(result.getInt("medicina"));
				usuario.setOrganico(result.getInt("organico"));
				usuario.setPapel(result.getInt("papel"));
				usuario.setPilas(result.getInt("pilas"));
				usuario.setPlastico(result.getInt("plastico"));
				usuario.setVidrio(result.getInt("vidrio"));
				usuario.setBarrio(result.getString("barrio"));
				usuario.setZona(result.getString("nombre"));

			} else {
				usuario = null;
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return usuario;
	}
	
	public void insertUsuario(Connection conn, Integer usuarioOID, Integer zonaOID, Usuario u ){
		PreparedStatement stmt = null;
        String sql = "INSERT INTO usuario ( usuarioOID, zonaOID, password, email, nick, basuraGenerada, recicla, vidrio, papel, plastico, organico, aceite, medicina, pilas, barrio, motivo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, usuarioOID);
            stmt.setInt(2, zonaOID);
            stmt.setString(3, u.getPassword());
            stmt.setString(4, u.getEmail());
            stmt.setString(5, u.getNick());
            stmt.setInt(6, u.getBasuraGenerada());
            stmt.setBoolean(7, u.getRecicla());
            stmt.setInt(8, u.getVidrio());
            stmt.setInt(9, u.getPapel());
            stmt.setInt(10, u.getPlastico());
            stmt.setInt(11, u.getOrganico());
            stmt.setInt(12, u.getAceite());
            stmt.setInt(13, u.getMedicamentos());
            stmt.setInt(14, u.getPilas());
            stmt.setString(15, u.getBarrio());
            stmt.setString(16, u.getMotivo());
            

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        
	}
	
	public int contarUsuariosNo(){
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		int res=0;

		try {
			
			String sql = "SELECT count(*) FROM Usuario WHERE recicla=0";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				res=result.getInt("count(*)");

			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return res;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public List selectAllUsuarios(Connection conn,Integer zonaOID) {

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE z.zonaOID=U.zonaOID and U.zonaOID = '"+zonaOID+"'";

			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setEmail(result.getString("email"));
				u.setNick(result.getString("nick"));
				u.setPassword(result.getString("password"));
				u.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					u.setRecicla(true);
				} else {
					u.setRecicla(false);
				}

				u.setAceite(result.getInt("aceite"));
				u.setMedicamentos(result.getInt("medicina"));
				u.setOrganico(result.getInt("organico"));
				u.setPapel(result.getInt("papel"));
				u.setPilas(result.getInt("pilas"));
				u.setPlastico(result.getInt("plastico"));
				u.setVidrio(result.getInt("vidrio"));
				u.setBarrio(result.getString("barrio"));
				u.setZona(result.getString("nombre"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}
}



